/**
 *====================================================
 * 文件名称: StatisticsEnterpriseActionApiImpl.java
 * 修订记录：
 * No    日期				作者(操作:具体内容)
 * 1.    2015年11月13日			chenxy(创建:创建文件)
 *====================================================
 * 类描述：(说明未实现或其它不应生成javadoc的内容)
 */
package com.yuexunit.finance.statistics.action.v1;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Session;

import com.yuexunit.finance.common.httpclient.HttpClientHelper;
import com.yuexunit.finance.common.util.DateUtils;
import com.yuexunit.finance.context.FinanceConstant;
import com.yuexunit.finance.context.FinanceSessionContext;
import com.yuexunit.finance.member.entity.EnterpriseEntity;
import com.yuexunit.finance.member.entity.EnterpriseGradeEntity;
import com.yuexunit.finance.member.entity.lexicon.EnterpriseTypeEnum;
import com.yuexunit.finance.member.service.EnterpriseGradeService;
import com.yuexunit.finance.member.service.EnterpriseService;
import com.yuexunit.finance.project.action.v1.model.ClientCreditLineDetail;
import com.yuexunit.finance.project.action.v1.model.ReceivePaymentDetail;
import com.yuexunit.finance.project.action.v1.model.output.ProductRankingDetail;
import com.yuexunit.finance.project.action.v1.model.output.RankingDetail;
import com.yuexunit.finance.project.entity.FinanceBulkstockEntity;
import com.yuexunit.finance.project.entity.FinanceMoneyStatementEntity;
import com.yuexunit.finance.project.entity.FinanceProductInnerEntity;
import com.yuexunit.finance.project.service.FinanceBulkstockService;
import com.yuexunit.finance.project.service.FinanceMoneyStatementService;
import com.yuexunit.finance.project.service.FinanceProductInnerService;
import com.yuexunit.finance.statistics.action.v1.dto.StatisticsEnterpriseCastor;
import com.yuexunit.finance.statistics.action.v1.model.EnterpriseAreas;
import com.yuexunit.finance.statistics.action.v1.model.EnterpriseFinanceSummaryDetail;
import com.yuexunit.finance.statistics.action.v1.model.EnterpriseSummaryDetail;
import com.yuexunit.finance.statistics.action.v1.model.ManagerBordDetail;
import com.yuexunit.finance.statistics.action.v1.model.NameAndValueInfo;
import com.yuexunit.finance.statistics.action.v1.model.ProcessFinanceApplication;
import com.yuexunit.finance.statistics.action.v1.model.SalesFunnelInfoResult;
import com.yuexunit.finance.system.entity.CapitalAccountInfoEntity;
import com.yuexunit.finance.system.service.BankInfoService;
import com.yuexunit.finance.tatistics.entity.EnterpriseFinanceSummaryEntity;
import com.yuexunit.finance.tatistics.service.EnterpriseFinanceSummaryService;
import com.yuexunit.inject.ComponentFactory;
import com.yuexunit.inject.annotation.Implements;
import com.yuexunit.jsonutil.JSON;
import com.yuexunit.page.PageCriteria;
import com.yuexunit.page.PageList;
import com.yuexunit.persist.hibernate.SessionRepository;
import com.yuexunit.util.DateUtil;
import com.yuexunit.util.StringUtil;

/**
 * @ClassName: StatisticsEnterpriseActionApiImpl
 * @Description: TODO(这里用一句话描述这个类的作用)
 * @author chenxy
 * @date 2015年11月13日 上午9:48:22
 */
@Implements(StatisticsEnterpriseActionApi.class)
public class StatisticsEnterpriseActionApiImpl implements StatisticsEnterpriseActionApi {

	private EnterpriseFinanceSummaryService enterpriseFinanceSummaryService = ComponentFactory.loadClass(EnterpriseFinanceSummaryService.class);

	private EnterpriseService enterpriseService = ComponentFactory.loadClass(EnterpriseService.class);

	private BankInfoService bankInfoService = ComponentFactory.loadClass(BankInfoService.class);

	private EnterpriseGradeService enterpriseGradeService = ComponentFactory.loadClass(EnterpriseGradeService.class);

	private FinanceProductInnerService financeProductInnerService = ComponentFactory.loadClass(FinanceProductInnerService.class);

	private FinanceMoneyStatementService financeMoneyStatementService = ComponentFactory.loadClass(FinanceMoneyStatementService.class);

	private FinanceBulkstockService financeBulkstockService = ComponentFactory.loadClass(FinanceBulkstockService.class);

	@Override
	public PageList<EnterpriseFinanceSummaryDetail> inquireEnterpriseFinanceSummaryPageList(Long enterpriseId, Integer enterpriseType, PageCriteria page) {
		try {
			if (FinanceSessionContext.getEnterpriseId() != null) {
				enterpriseId = FinanceSessionContext.getEnterpriseId();
			}
		} catch (Exception e) {
		}

		if (enterpriseId == null && (FinanceSessionContext.getAppKey().equals(FinanceConstant.APPKEY_ENTERPRISE_WEB) || FinanceSessionContext.getAppKey().equals(FinanceConstant.APPKEY_ENTERPRISE_IOS) || FinanceSessionContext.getAppKey().equals(FinanceConstant.APPKEY_ENTERPRISE_ANDROID))) {
			return null;
		}
		PageList<EnterpriseFinanceSummaryEntity> pageList = enterpriseFinanceSummaryService.inquireEnterpriseFinanceSummaryPageList(enterpriseId, enterpriseType, page);
		PageList<EnterpriseFinanceSummaryDetail> resultList = new PageList<>(page, pageList.getTotalRecordCount());
		for (EnterpriseFinanceSummaryEntity entity : pageList) {
			EnterpriseEntity enterprise = enterpriseService.inquireEnterprise(entity.getEnterpriseId());
			if (enterprise!= null && Boolean.TRUE == enterprise.getEnabled()) {
				resultList.add(StatisticsEnterpriseCastor.toEnterpriseFinanceSummaryDetail(entity));
			}
		}
		return resultList;
	}

	@Override
	public ManagerBordDetail inquireManagerBordDetail() {
		ManagerBordDetail detail = new ManagerBordDetail();
		Session session = SessionRepository.getCurrentSession();

		//直客授信额度
		String sql = "SELECT SUM(s1) sum1, SUM(s2) sum2 FROM (SELECT b.ENTERPRISE_NAME, SUM(a.CREDIT_ALREADY) s2, SUM(a.CREDIT_LINE) s1 from fin_enterprise_grade a, fin_enterprise b WHERE a.ENTERPRISE_ID = b.ENTERPRISE_ID AND b.ENTERPRISE_TYPE = 0 GROUP BY a.ENTERPRISE_ID) t1;";
		List<Object> datas = session.createSQLQuery(sql).addScalar("sum1").addScalar("sum2").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			detail.setEnterpriseCreditLine((BigDecimal)os[0]);
			detail.setEnterpriseCreditLineAlready((BigDecimal)os[1]);
		}

		//分销商授信额度
		sql = "SELECT SUM(s1) sum1, SUM(s2) sum2 FROM (SELECT b.ENTERPRISE_NAME, SUM(a.CREDIT_ALREADY) s2, SUM(a.CREDIT_LINE) s1 from fin_enterprise_grade a, fin_enterprise b WHERE a.ENTERPRISE_ID = b.ENTERPRISE_ID AND b.ENTERPRISE_TYPE = 1 GROUP BY a.ENTERPRISE_ID) t1;";
		datas = session.createSQLQuery(sql).addScalar("sum1").addScalar("sum2").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			detail.setDistributorsCreditLine((BigDecimal)os[0]);
			detail.setDistributorsCreditLineAlready((BigDecimal)os[1]);
		}

		//累计放款 累计还款
		sql = "SELECT SUM(a.DISBURSEMENT_AMOUNT) s1, SUM(a.RECEIPT_AMOUNT) s2 FROM fin_finance_money_statement a WHERE a.STATEMENT_SOURCE = 'LOAN_NOTICE' OR a.STATEMENT_SOURCE = 'RECEIVABLES';";
		datas = session.createSQLQuery(sql).addScalar("s1").addScalar("s2").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			detail.setLoanAmount((BigDecimal)os[0]);
			detail.setReceiveAmount((BigDecimal)os[1]);
		}

		//当月放款 当月还款
		sql = "SELECT SUM(a.DISBURSEMENT_AMOUNT) s1, SUM(a.RECEIPT_AMOUNT) s2 FROM fin_finance_money_statement a WHERE (a.STATEMENT_SOURCE = 'LOAN_NOTICE' OR a.STATEMENT_SOURCE = 'RECEIVABLES') AND a.STATEMENT_D8 >= " + DateUtils.parseDateInt(DateUtils.getFirstDay()) + " AND a.STATEMENT_D8 <= " + DateUtils.parseDateInt(DateUtils.getLastDay()) + ";";
		datas = session.createSQLQuery(sql).addScalar("s1").addScalar("s2").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			detail.setCurrentLoanAmount((BigDecimal)os[0]);
			detail.setCurrentReceiveAmount((BigDecimal)os[1]);
		}

		//当日放款 当日还款
		sql = "SELECT SUM(a.DISBURSEMENT_AMOUNT) s1, SUM(a.RECEIPT_AMOUNT) s2 FROM fin_finance_money_statement a WHERE (a.STATEMENT_SOURCE = 'LOAN_NOTICE' OR a.STATEMENT_SOURCE = 'RECEIVABLES') AND a.STATEMENT_D8 = " + DateUtil.todayInt() + ";";
		datas = session.createSQLQuery(sql).addScalar("s1").addScalar("s2").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			detail.setTodayLoanAmount((BigDecimal)os[0]);
			detail.setTodayReceiveAmount((BigDecimal)os[1]);
		}

		//实收利息
		sql = "SELECT SUM(a.RECEIPT_AMOUNT) s FROM fin_finance_money_statement a WHERE a.STATEMENT_SOURCE = 'QUARTER_SETTLEMENT';";
		datas = session.createSQLQuery(sql).addScalar("s").list();
		for (Object l : datas) {
			detail.setInterestAmount((BigDecimal)l);
		}

		List<CapitalAccountInfoEntity> caies = bankInfoService.inquireBankInfoList();
		for (CapitalAccountInfoEntity caie : caies) {
			detail.setCapital(detail.getCapital().add(caie.getAccountBalance()));
		}
		SessionRepository.closeCurrentSession();
		return detail;
	}

	@Override
	public EnterpriseFinanceSummaryDetail inquireEnterpriseFinanceSummaryDetail(Long enterpriseId) {

		EnterpriseFinanceSummaryDetail detail = new EnterpriseFinanceSummaryDetail();
		Session session = SessionRepository.getCurrentSession();

		//历史融资笔数
		String sql = "SELECT ENTERPRISE_ID, COUNT(1) FINANCE_COUNT FROM FIN_FINANCE_APPLICATION WHERE ENTERPRISE_ID = " + enterpriseId + ";";
		List<Object> datas = session.createSQLQuery(sql).addScalar("ENTERPRISE_ID").addScalar("FINANCE_COUNT").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			detail.setFinanceCount((Integer)os[1]);
		}

		//进行中的融资笔数
		sql = "SELECT ENTERPRISE_ID, COUNT(1) PROGRESSING FROM FIN_FINANCE_APPLICATION WHERE FINANCE_STATE != 0 AND FINANCE_STATE != 4 AND FINANCE_STATE != 18 AND FINANCE_STATE != 19 AND FINANCE_STATE != 20 AND ENTERPRISE_ID" + enterpriseId + ";";
		datas = session.createSQLQuery(sql).addScalar("ENTERPRISE_ID").addScalar("PROGRESSING").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			detail.setProgressing((Integer)os[1]);
		}

		//异常融资笔数
		sql = "SELECT ENTERPRISE_ID, COUNT(1) ABNORMAL FROM FIN_CONTAINER_INVENTORY WHERE ABNORMAL = 0 AND ENTERPRISE_ID = " + enterpriseId + ";";
		datas = session.createSQLQuery(sql).addScalar("ENTERPRISE_ID").addScalar("ABNORMAL").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			detail.setProgressing((Integer)os[1]);
		}

		//赎货笔数
		sql = "SELECT ENTERPRISE_ID, COUNT(1) REDEEM FROM FIN_PICKUP_APPLICATION WHERE APPLICATION_STATE IN (2, 4, 5, 6) AND PICKUP_TYPE = 3 AND ENTERPRISE_ID = " + enterpriseId + ";";
		datas = session.createSQLQuery(sql).addScalar("ENTERPRISE_ID").addScalar("ABNORMAL").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			detail.setProgressing((Integer)os[1]);
		}

		//异常融资笔数
		sql = "SELECT ENTERPRISE_ID, COUNT(1) ABNORMAL FROM FIN_CONTAINER_INVENTORY WHERE ABNORMAL = 0 AND ENTERPRISE_ID = " + enterpriseId + ";";
		datas = session.createSQLQuery(sql).addScalar("ENTERPRISE_ID").addScalar("ABNORMAL").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			detail.setProgressing((Integer)os[1]);
		}

		//异常融资笔数
		sql = "SELECT ENTERPRISE_ID, COUNT(1) ABNORMAL FROM FIN_CONTAINER_INVENTORY WHERE ABNORMAL = 0 AND ENTERPRISE_ID = " + enterpriseId + ";";
		datas = session.createSQLQuery(sql).addScalar("ENTERPRISE_ID").addScalar("ABNORMAL").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			detail.setProgressing((Integer)os[1]);
		}

		//异常融资笔数
		sql = "SELECT ENTERPRISE_ID, COUNT(1) ABNORMAL FROM FIN_CONTAINER_INVENTORY WHERE ABNORMAL = 0 AND ENTERPRISE_ID = " + enterpriseId + ";";
		datas = session.createSQLQuery(sql).addScalar("ENTERPRISE_ID").addScalar("ABNORMAL").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			detail.setProgressing((Integer)os[1]);
		}

		SessionRepository.closeCurrentSession();
		return null;

	}

	@Override
	public List<RankingDetail> inquireEnterpriseArea() {
		Session session = SessionRepository.getCurrentSession();

		String sql = "SELECT PROVINCE p, COUNT(PROVINCE) c FROM fin_enterprise a WHERE PROVINCE IS NOT NULL AND PROVINCE != '' GROUP BY PROVINCE ORDER BY c DESC;";
		List<Object> datas = session.createSQLQuery(sql).addScalar("p").addScalar("c").list();

		List<RankingDetail> rds = new ArrayList<>();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			RankingDetail rd = new RankingDetail();
			rd.setName((String)os[0]);
			rd.setValue(new BigDecimal((BigInteger)os[1]));
			rds.add(rd);
		}
		SessionRepository.closeCurrentSession();
		return rds;
	}

	@Override
	public List<RankingDetail> inquireRankingDatas(Integer type, Long enterpriseId, Long bulkstockInfoId) {
		Session session = SessionRepository.getCurrentSession();

		List<RankingDetail> list = new ArrayList<>();
		if (type == 1) { //货品
			String sql = "SELECT a.BULKSTOCK_INFO_ID, a.BULKSTOCK_NAME, b.SPECIFICATION_NAME ,SUM(a.FINANCE_PRICE*a.VALUATION_QUANTITY) as s from fin_finance_bulkstock a, fin_bulkstock_info b WHERE a.BULKSTOCK_INFO_ID = b.BULKSTOCK_INFO_ID GROUP BY a.BULKSTOCK_INFO_ID ORDER BY s DESC;";
			List<Object> datas = session.createSQLQuery(sql).addScalar("BULKSTOCK_INFO_ID").addScalar("BULKSTOCK_NAME").addScalar("SPECIFICATION_NAME").addScalar("s").list();
			for (Object l : datas) {
				Object[] os = (Object[])l;
				RankingDetail detail = new RankingDetail();
				detail.setDetailId(((BigInteger)os[0]).longValue());
				detail.setName((String)os[1] + "(" + (String)os[2] + ")");
				detail.setValue((BigDecimal)os[3]);
				list.add(detail);
			}
		} else if (type == 2) { //客户
			String sql = "SELECT a.RECEIPT_ID, a.RECEIPT_NAME, b.ENTERPRISE_SHORT_NAME, SUM(a.DISBURSEMENT_AMOUNT) s from fin_finance_money_statement a, fin_enterprise b WHERE STATEMENT_SOURCE = 'LOAN_NOTICE' AND a.RECEIPT_ID = b.ENTERPRISE_ID GROUP BY RECEIPT_ID ORDER BY DISBURSEMENT_AMOUNT DESC;";
			List<Object> datas = session.createSQLQuery(sql).addScalar("RECEIPT_ID").addScalar("RECEIPT_NAME").addScalar("s").addScalar("ENTERPRISE_SHORT_NAME").list();
			for (Object l : datas) {
				Object[] os = (Object[])l;
				RankingDetail detail = new RankingDetail();
				if (os[0] == null) {
					continue;
				}
				detail.setDetailId(((BigInteger)os[0]).longValue());
				if (os[3] != null) {
					detail.setName((String)os[3]);
				} else {
					detail.setName((String)os[1]);
				}
				detail.setValue((BigDecimal)os[2]);
				list.add(detail);
			}
		} else if (type == 4) { //客户+货品
			String sql = "SELECT a.BULKSTOCK_INFO_ID, a.BULKSTOCK_NAME, c.SPECIFICATION_NAME, SUM(a.FINANCE_PRICE*a.VALUATION_QUANTITY) s FROM fin_finance_bulkstock a, fin_finance_application b, fin_bulkstock_info c WHERE a.FINANCE_ID = b.FINANCE_ID AND b.ENTERPRISE_ID = " + enterpriseId + " AND a.BULKSTOCK_INFO_ID = c.BULKSTOCK_INFO_ID GROUP BY a.BULKSTOCK_INFO_ID ORDER BY s DESC;";
			List<Object> datas = session.createSQLQuery(sql).addScalar("BULKSTOCK_INFO_ID").addScalar("BULKSTOCK_NAME").addScalar("SPECIFICATION_NAME").addScalar("s").list();
			for (Object l : datas) {
				Object[] os = (Object[])l;
				RankingDetail detail = new RankingDetail();
				if (os[0] == null) {
					continue;
				}
				detail.setDetailId(((BigInteger)os[0]).longValue());
				detail.setName((String)os[1] + "(" + (String)os[2] + ")");
				detail.setValue((BigDecimal)os[3]);
				list.add(detail);
			}
		} else if (type == 3) { //货品+客户
			String sql = "SELECT c.ENTERPRISE_ID, c.ENTERPRISE_NAME, a.BULKSTOCK_NAME, b.SPECIFICATION_NAME, SUM(a.FINANCE_PRICE*a.VALUATION_QUANTITY) s, d.ENTERPRISE_SHORT_NAME FROM fin_finance_bulkstock a, fin_bulkstock_info b, fin_finance_application c, fin_enterprise d WHERE a.BULKSTOCK_INFO_ID = " + bulkstockInfoId + " and a.BULKSTOCK_INFO_ID = b.BULKSTOCK_INFO_ID and a.FINANCE_ID = c.FINANCE_ID AND c.ENTERPRISE_ID = d.ENTERPRISE_ID GROUP BY c.ENTERPRISE_ID ORDER BY s DESC;";
			List<Object> datas = session.createSQLQuery(sql).addScalar("ENTERPRISE_ID").addScalar("ENTERPRISE_NAME").addScalar("s").addScalar("ENTERPRISE_SHORT_NAME").list();
			for (Object l : datas) {
				Object[] os = (Object[])l;
				RankingDetail detail = new RankingDetail();
				if (os[0] == null) {
					continue;
				}
				detail.setDetailId(((BigInteger)os[0]).longValue());
				if ((String)os[3] != null) {
					detail.setName((String)os[3]);
				} else {
					detail.setName((String)os[1]);
				}
				detail.setValue((BigDecimal)os[2]);
				list.add(detail);
			}
		}
		SessionRepository.closeCurrentSession();
		return list;
	}

	@Override
	public ReceivePaymentDetail inqurieReceivePaymentDetail(Integer type) {
		ReceivePaymentDetail detail = new ReceivePaymentDetail();
		Session session = SessionRepository.getCurrentSession();
		String sql = "";
		int c = 1;
		if (1 == type) {//累计收付款
			sql = "SELECT * FROM (SELECT SUBSTR(concat(a.STATEMENT_D8),1,6) D8, SUM(a.DISBURSEMENT_AMOUNT) PAYMENT, SUM(a.RECEIPT_AMOUNT) RECEIPT, (SUM(a.RECEIPT_AMOUNT) - SUM(a.DISBURSEMENT_AMOUNT)) NETINFLOWS, (SUM(a.DISBURSEMENT_AMOUNT) - SUM(a.RECEIPT_AMOUNT)) STOCK FROM fin_finance_money_statement a WHERE a.STATEMENT_SOURCE = 'LOAN_NOTICE' OR a.STATEMENT_SOURCE = 'RECEIVABLES' GROUP BY D8 ORDER BY D8) t WHERE D8 >= '" + DateUtil.getDateInt(DateUtils.getCurrYearFirst()) + "' AND D8 <= '" + DateUtil.todayInt() + "';";
		} else if (2 == type) {//当月收付款
			sql = "SELECT * FROM (SELECT SUBSTR(concat(a.STATEMENT_D8),1,6) D8, SUM(a.DISBURSEMENT_AMOUNT) PAYMENT, SUM(a.RECEIPT_AMOUNT) RECEIPT, (SUM(a.RECEIPT_AMOUNT) - SUM(a.DISBURSEMENT_AMOUNT)) NETINFLOWS, (SUM(a.DISBURSEMENT_AMOUNT) - SUM(a.RECEIPT_AMOUNT)) STOCK FROM fin_finance_money_statement a WHERE a.STATEMENT_SOURCE = 'LOAN_NOTICE' OR a.STATEMENT_SOURCE = 'RECEIVABLES' GROUP BY D8 ORDER BY D8) t WHERE D8 >= '" + DateUtil.getDateInt(DateUtils.addDateField(DateUtil.now(), Calendar.MONTH, -1)) + "' AND D8 <= '" + DateUtil.todayInt() + "';";
		} else if (type == 3) {//近七天收付款
			sql = "SELECT * FROM (SELECT concat(a.STATEMENT_D8) D8, SUM(a.DISBURSEMENT_AMOUNT) PAYMENT, SUM(a.RECEIPT_AMOUNT) RECEIPT, (SUM(a.RECEIPT_AMOUNT) - SUM(a.DISBURSEMENT_AMOUNT)) NETINFLOWS, (SUM(a.DISBURSEMENT_AMOUNT) - SUM(a.RECEIPT_AMOUNT)) STOCK FROM fin_finance_money_statement a WHERE a.STATEMENT_SOURCE = 'LOAN_NOTICE' OR a.STATEMENT_SOURCE = 'RECEIVABLES' GROUP BY D8 ORDER BY D8) t WHERE D8 >= '" + DateUtil.getDateInt(DateUtils.addDateField(DateUtil.now(), Calendar.DATE, -7)) + "' AND D8 <= '" + DateUtil.todayInt() + "';";
			c = 7;
		} else if (type == 4) {//近三十天收付款
			sql = "SELECT * FROM (SELECT concat(a.STATEMENT_D8) D8, SUM(a.DISBURSEMENT_AMOUNT) PAYMENT, SUM(a.RECEIPT_AMOUNT) RECEIPT, (SUM(a.RECEIPT_AMOUNT) - SUM(a.DISBURSEMENT_AMOUNT)) NETINFLOWS, (SUM(a.DISBURSEMENT_AMOUNT) - SUM(a.RECEIPT_AMOUNT)) STOCK FROM fin_finance_money_statement a WHERE a.STATEMENT_SOURCE = 'LOAN_NOTICE' OR a.STATEMENT_SOURCE = 'RECEIVABLES' GROUP BY D8 ORDER BY D8) t WHERE D8 >= '" + DateUtil.getDateInt(DateUtils.addDateField(DateUtil.now(), Calendar.DATE, -30)) + "' AND D8 <= '" + DateUtil.todayInt() + "';";
			c = 30;
		}
		List<Object> datas = session.createSQLQuery(sql).addScalar("D8").addScalar("PAYMENT").addScalar("RECEIPT").addScalar("NETINFLOWS").addScalar("STOCK").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			detail.getDates().add((String)os[0]);
			detail.getPayments().add(((BigDecimal)os[1]).divide(new BigDecimal(10000)));
			detail.getReceives().add(((BigDecimal)os[2]).divide(new BigDecimal(10000)));
			detail.getNetInflows().add(((BigDecimal)os[3]).divide(new BigDecimal(10000)));
			detail.getStock().add(((BigDecimal)os[4]).divide(new BigDecimal(10000)));
		}
		SessionRepository.closeCurrentSession();

		if (type == 3 || type == 4) {
			ReceivePaymentDetail d = new ReceivePaymentDetail();
			int j = 0;
			for (int i = 0; i < c; i++) {
				if (detail.getDates().size() > j && detail.getDates().get(j).equals(DateUtil.getDateInt(DateUtils.addDateField(DateUtil.now(), Calendar.DATE, i - c)) + "")) {
					d.getDates().add(detail.getDates().get(j));
					d.getPayments().add(detail.getPayments().get(j));
					d.getReceives().add(detail.getReceives().get(j));
					d.getNetInflows().add(detail.getNetInflows().get(j));
					d.getStock().add(detail.getStock().get(j));
					j++;
				} else {
					d.getDates().add(DateUtil.getDateInt(DateUtils.addDateField(DateUtil.now(), Calendar.DATE, i - c)) + "");
					d.getPayments().add(BigDecimal.ZERO);
					d.getReceives().add(BigDecimal.ZERO);
					d.getNetInflows().add(BigDecimal.ZERO);
					d.getStock().add(BigDecimal.ZERO);
				}
			}

			return d;
		}
		return detail;
	}

	@Override
	public List<ClientCreditLineDetail> inqurieReceivePaymentList(Integer year, Integer type, Boolean distributors) {
		List<EnterpriseEntity> list = null;
		if (null == distributors) {
			list = enterpriseService.inquireEnterpriseList(null);
		} else if (Boolean.TRUE == distributors) {
			list = enterpriseService.inquireEnterpriseList(EnterpriseTypeEnum.DISTRIBUTORS);
		} else {
			list = enterpriseService.inquireEnterpriseList(EnterpriseTypeEnum.CUSTOMER);
		}

		Date sd = null;
		Date ed = null;
		if (year == null) {
			sd = DateUtils.getCurrYearFirst();
		} else {
			sd = DateUtils.getYearFirst(year);
		}

		if (year == null) {
			ed = DateUtils.getCurrYearLast();
		} else {
			ed = DateUtils.getYearLast(year);
		}
		List<ClientCreditLineDetail> details = new ArrayList<>();
		for (EnterpriseEntity ee : list) {
			EnterpriseGradeEntity ege = enterpriseGradeService.inquireEnterpriseGrade(ee.getEnterpriseId());
			if (ege == null) {
				continue;
			}
			ClientCreditLineDetail detail = new ClientCreditLineDetail();
			detail.setPayment(BigDecimal.ZERO);
			detail.setReceive(BigDecimal.ZERO);
			detail.setInterest(BigDecimal.ZERO);
			detail.setEnterpriseId(ee.getEnterpriseId());
			detail.setEnterpriseName(ee.getEnterpriseName());
			List<FinanceMoneyStatementEntity> fmses = null;
			if (type == 1) {
				//				fmses = financeMoneyStatementService.inquireFinanceMoneyStatementList1(ee.getEnterpriseId(), new String[]{"QUARTER_SETTLEMENT", "LOAN_NOTICE", "RECEIVABLES"}, DateUtil.getDateInt(DateUtils.addDateField(DateUtil.now(), Calendar.YEAR, -1)), DateUtil.todayInt());
				fmses = financeMoneyStatementService.inquireFinanceMoneyStatementList1(ee.getEnterpriseId(), new String[]{"QUARTER_SETTLEMENT", "LOAN_NOTICE", "RECEIVABLES"}, DateUtils.parseDateInt(sd), DateUtils.parseDateInt(ed));
				detail.setStartDate(DateUtil.formatDate(DateUtils.addDateField(DateUtil.now(), Calendar.YEAR, -1)));
				detail.setEndDate(DateUtil.nowDate());
			} else if (type == 2) {
				fmses = financeMoneyStatementService.inquireFinanceMoneyStatementList1(ee.getEnterpriseId(), new String[]{"QUARTER_SETTLEMENT", "LOAN_NOTICE", "RECEIVABLES"}, DateUtils.parseDateInt(DateUtils.getFirstDay()), DateUtils.parseDateInt(DateUtils.getLastDay()));
				detail.setStartDate(DateUtil.formatDate(DateUtils.getFirstDay()));
				detail.setEndDate(DateUtil.formatDate(DateUtils.getLastDay()));
			} else if (type == 3) {
				fmses = financeMoneyStatementService.inquireFinanceMoneyStatementList1(ee.getEnterpriseId(), new String[]{"QUARTER_SETTLEMENT", "LOAN_NOTICE", "RECEIVABLES"}, DateUtil.todayInt(), DateUtil.todayInt());
				detail.setStartDate(DateUtil.nowDate());
				detail.setEndDate(DateUtil.nowDate());
			}

			for (FinanceMoneyStatementEntity fmse : fmses) {
				if ("LOAN_NOTICE".equals(fmse.getStatementSource())) {
					detail.setPayment(detail.getPayment().add(fmse.getDisbursementAmount()));
				} else if ("RECEIVABLES".equals(fmse.getStatementSource())){
					detail.setReceive(detail.getReceive().add(fmse.getReceiptAmount()));
				} else if ("QUARTER_SETTLEMENT".equals(fmse.getStatementSource())) {
					detail.setInterest(detail.getInterest().add(fmse.getReceiptAmount()));
				}
			}
			detail.setCreditLine(ege.getCreditLine());
			if (ege.getCreditLine().subtract(ege.getCreditAlready()).compareTo(BigDecimal.ZERO)<0) {
				detail.setCreditLineRemain(BigDecimal.ZERO);
			} else {
				detail.setCreditLineRemain(ege.getCreditLine().subtract(ege.getCreditAlready()));
			}

			details.add(detail);
		}
		return details;
	}

	@Override
	public List<ProductRankingDetail> inquireLoanRankingDatas() {
		List<ProductRankingDetail> result = new ArrayList<>();
		Session session = SessionRepository.getCurrentSession();
		//		WHERE D8 >= '" + DateUtil.getDateInt(DateUtils.getCurrYearFirst()) + "' AND D8 <= '" + DateUtil.todayInt() + "'
		//舱单代采 productType=1
		String sql = "SELECT DATE_FORMAT(a.STATEMENT_D8, '%Y%m') months, SUM(a.DISBURSEMENT_AMOUNT) s, b.PRODUCT_TYPE p FROM fin_finance_money_statement a, fin_finance_application b WHERE a.STATEMENT_SOURCE = 'LOAN_NOTICE' AND a.FINANCE_ID = b.FINANCE_ID AND b.PRODUCT_TYPE = 1 AND a.STATEMENT_D8 >= '" + DateUtil.getDateInt(DateUtils.getCurrYearFirst()) + "' AND a.STATEMENT_D8 <= '" + DateUtil.todayInt() + "' GROUP BY months, p ORDER BY months;";
		System.out.println("==========");
		List<Object> datas = session.createSQLQuery(sql).addScalar("months").addScalar("s").addScalar("p").list();
		Map<String, ProductRankingDetail> prdMap = new HashMap<>();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			ProductRankingDetail detail = prdMap.get((String)os[0]);
			if (detail == null) {
				detail = new ProductRankingDetail();
			}
			detail.setName((String)os[0]);
			detail.setValue1((BigDecimal)os[1]);
			prdMap.put((String)os[0], detail);
		}

		//舱单质押 productType=2
		sql = "SELECT DATE_FORMAT(a.STATEMENT_D8, '%Y%m') months, SUM(a.DISBURSEMENT_AMOUNT) s, b.PRODUCT_TYPE p FROM fin_finance_money_statement a, fin_finance_application b WHERE a.STATEMENT_SOURCE = 'LOAN_NOTICE' AND a.FINANCE_ID = b.FINANCE_ID AND b.PRODUCT_TYPE = 2 AND a.STATEMENT_D8 >= '" + DateUtil.getDateInt(DateUtils.getCurrYearFirst()) + "' AND a.STATEMENT_D8 <= '" + DateUtil.todayInt() + "' GROUP BY months, p ORDER BY months;";
		datas = session.createSQLQuery(sql).addScalar("months").addScalar("s").addScalar("p").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			ProductRankingDetail detail = prdMap.get((String)os[0]);
			if (detail == null) {
				detail = new ProductRankingDetail();
			}
			detail.setName((String)os[0]);
			detail.setValue2((BigDecimal)os[1]);
			prdMap.put((String)os[0], detail);
		}

		//仓储质押 productType=3
		sql = "SELECT DATE_FORMAT(a.STATEMENT_D8, '%Y%m') months, SUM(a.DISBURSEMENT_AMOUNT) s, b.PRODUCT_TYPE p FROM fin_finance_money_statement a, fin_finance_application b WHERE a.STATEMENT_SOURCE = 'LOAN_NOTICE' AND a.FINANCE_ID = b.FINANCE_ID AND b.PRODUCT_TYPE = 3 AND a.STATEMENT_D8 >= '" + DateUtil.getDateInt(DateUtils.getCurrYearFirst()) + "' AND a.STATEMENT_D8 <= '" + DateUtil.todayInt() + "' GROUP BY months, p ORDER BY months;";
		datas = session.createSQLQuery(sql).addScalar("months").addScalar("s").addScalar("p").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			ProductRankingDetail detail = prdMap.get((String)os[0]);
			if (detail == null) {
				detail = new ProductRankingDetail();
			}
			detail.setName((String)os[0]);
			detail.setValue3((BigDecimal)os[1]);
			prdMap.put((String)os[0], detail);
		}

		//运费保理 productType=4
		sql = "SELECT DATE_FORMAT(a.STATEMENT_D8, '%Y%m') months, SUM(a.DISBURSEMENT_AMOUNT) s, b.PRODUCT_TYPE p FROM fin_finance_money_statement a, fin_finance_application b WHERE a.STATEMENT_SOURCE = 'LOAN_NOTICE' AND a.FINANCE_ID = b.FINANCE_ID AND b.PRODUCT_TYPE = 4 AND a.STATEMENT_D8 >= '" + DateUtil.getDateInt(DateUtils.getCurrYearFirst()) + "' AND a.STATEMENT_D8 <= '" + DateUtil.todayInt() + "' GROUP BY months, p ORDER BY months;";
		datas = session.createSQLQuery(sql).addScalar("months").addScalar("s").addScalar("p").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			ProductRankingDetail detail = prdMap.get((String)os[0]);
			if (detail == null) {
				detail = new ProductRankingDetail();
			}
			detail.setName((String)os[0]);
			detail.setValue4((BigDecimal)os[1]);
			prdMap.put((String)os[0], detail);
		}

		//代理采购 productType=5
		sql = "SELECT DATE_FORMAT(a.STATEMENT_D8, '%Y%m') months, SUM(a.DISBURSEMENT_AMOUNT) s, b.PRODUCT_TYPE p FROM fin_finance_money_statement a, fin_finance_application b WHERE a.STATEMENT_SOURCE = 'LOAN_NOTICE' AND a.FINANCE_ID = b.FINANCE_ID AND b.PRODUCT_TYPE = 5 AND a.STATEMENT_D8 >= '" + DateUtil.getDateInt(DateUtils.getCurrYearFirst()) + "' AND a.STATEMENT_D8 <= '" + DateUtil.todayInt() + "' GROUP BY months, p ORDER BY months;";
		datas = session.createSQLQuery(sql).addScalar("months").addScalar("s").addScalar("p").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			ProductRankingDetail detail = prdMap.get((String)os[0]);
			if (detail == null) {
				detail = new ProductRankingDetail();
			}
			detail.setName((String)os[0]);
			detail.setValue5((BigDecimal)os[1]);
			prdMap.put((String)os[0], detail);
		}

		//代理销售 productType=6
		sql = "SELECT DATE_FORMAT(a.STATEMENT_D8, '%Y%m') months, SUM(a.DISBURSEMENT_AMOUNT) s, b.PRODUCT_TYPE p FROM fin_finance_money_statement a, fin_finance_application b WHERE a.STATEMENT_SOURCE = 'LOAN_NOTICE' AND a.FINANCE_ID = b.FINANCE_ID AND b.PRODUCT_TYPE = 6 AND a.STATEMENT_D8 >= '" + DateUtil.getDateInt(DateUtils.getCurrYearFirst()) + "' AND a.STATEMENT_D8 <= '" + DateUtil.todayInt() + "' GROUP BY months, p ORDER BY months;";
		datas = session.createSQLQuery(sql).addScalar("months").addScalar("s").addScalar("p").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			ProductRankingDetail detail = prdMap.get((String)os[0]);
			if (detail == null) {
				detail = new ProductRankingDetail();
			}
			detail.setName((String)os[0]);
			detail.setValue6((BigDecimal)os[1]);
			prdMap.put((String)os[0], detail);
		}

		for(String k : prdMap.keySet()){
			result.add(prdMap.get(k));
		}

		Collections.sort(result);  
		return result;
	}

	@Override
	public ReceivePaymentDetail inqurieReceivePaymentListByArea() {
		ReceivePaymentDetail detail = new ReceivePaymentDetail();
		Session session = SessionRepository.getCurrentSession();

		String sql = "SELECT * FROM (SELECT SUBSTR(concat(a.STATEMENT_D8),1,6) D8, c.PROVINCE province, SUM(a.DISBURSEMENT_AMOUNT) PAYMENT, SUM(a.RECEIPT_AMOUNT) RECEIPT, ( SUM(a.RECEIPT_AMOUNT) - SUM(a.DISBURSEMENT_AMOUNT)) NETINFLOWS, ( SUM(a.DISBURSEMENT_AMOUNT) - SUM(a.RECEIPT_AMOUNT)) STOCK FROM fin_finance_money_statement a, fin_finance_application b, fin_enterprise c WHERE ( a.STATEMENT_SOURCE = 'LOAN_NOTICE' OR a.STATEMENT_SOURCE = 'RECEIVABLES' ) AND a.FINANCE_ID = b.FINANCE_ID AND b.ENTERPRISE_ID = c.ENTERPRISE_ID GROUP BY province ) t WHERE D8 >= '" + DateUtil.getDateInt(DateUtils.getCurrYearFirst()) + "' AND D8 <= '" + DateUtil.todayInt() + "' ORDER BY PAYMENT;";
		List<Object> datas = session.createSQLQuery(sql).addScalar("province").addScalar("PAYMENT").addScalar("RECEIPT").addScalar("NETINFLOWS").addScalar("STOCK").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			detail.getDates().add((String)os[0]);
			detail.getPayments().add(((BigDecimal)os[1]).divide(new BigDecimal(10000)));
			detail.getReceives().add(((BigDecimal)os[2]).divide(new BigDecimal(10000)));
			detail.getNetInflows().add(((BigDecimal)os[3]).divide(new BigDecimal(10000)));
			detail.getStock().add(((BigDecimal)os[4]).divide(new BigDecimal(10000)));
		}
		SessionRepository.closeCurrentSession();
		return detail;
	}

	@Override
	public EnterpriseAreas inquireEnterpriseLocation() {
		Session session = SessionRepository.getCurrentSession();
		EnterpriseAreas ea = new EnterpriseAreas();
		String sql = "SELECT a.CITY city, a.LNG lng, a.LAT lat FROM fin_enterprise a;";
		Map<String, List<BigDecimal>> map = new HashMap<>();

		List<Object> datas = session.createSQLQuery(sql).addScalar("city").addScalar("lng").addScalar("LAT").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			if (StringUtil.isNotBlank((String)os[0])) {
				BigDecimal lng = new BigDecimal((String)os[1]);
				BigDecimal lat = new BigDecimal((String)os[2]);
				if (lng != null && lat != null) {
					List<BigDecimal> d = new ArrayList<>();
					d.add(lng);
					d.add(lat);
					map.put((String)os[0], d);
				}
			}
		}

		List<NameAndValueInfo> navis = new ArrayList<>();
		//数量
		//		sql = "SELECT a.CITY city, COUNT(*) c FROM fin_enterprise a GROUP BY CITY;";
		//金额
		sql = "SELECT a.CITY city, SUM(b.DISBURSEMENT_AMOUNT) s FROM fin_enterprise a, fin_finance_money_statement b, fin_finance_application c WHERE a.ENTERPRISE_ID = c.ENTERPRISE_ID AND b.FINANCE_ID = c.FINANCE_ID GROUP BY city;";
		datas = session.createSQLQuery(sql).addScalar("city").addScalar("s").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			if (StringUtil.isNotBlank((String)os[0])) {
				NameAndValueInfo navi = new NameAndValueInfo();
				navi.setName((String)os[0]);
				navi.setValue((BigDecimal)os[1]);
				navis.add(navi);
			}
		}

		ea.setGeoCoordMap(map);
		ea.setCurrentData(navis);

		SessionRepository.closeCurrentSession();
		return ea;
	}

	@Override
	public SalesFunnelInfoResult getSalesFunnelReportforms() {
		Map<String, String> params = new HashMap<>();

		String responseText = HttpClientHelper.post("http://122.152.210.195:8099/crm/interfaceJournaling/getSalesFunnelReportforms", params);
		System.out.println(responseText);
		SalesFunnelInfoResult result = JSON.toBean(responseText, SalesFunnelInfoResult.class);
		return result;
	}

	@Override
	public List<NameAndValueInfo> inquireFollowPersonRanking() {
		Session session = SessionRepository.getCurrentSession();
		EnterpriseAreas ea = new EnterpriseAreas();
		String sql = "SELECT a.FOLLOW_NAME AS f, SUM(c.RECEIPT_AMOUNT) AS r FROM fin_follow_person a, fin_finance_application b, fin_finance_money_statement c WHERE b.FINANCE_ID = c.FINANCE_ID AND a.ENTERPRISE_ID = b.ENTERPRISE_ID AND (c.STATEMENT_SOURCE = 'QUARTER_SETTLEMENT_INTEREST' OR c.STATEMENT_SOURCE = 'QUARTER_SETTLEMENT') AND c.STATEMENT_D8 >= '" + DateUtil.getDateInt(DateUtils.getCurrYearFirst()) + "' AND c.STATEMENT_D8 <= '" + DateUtil.todayInt() + "' GROUP BY f ORDER BY r DESC";
		Map<String, List<BigDecimal>> map = new HashMap<>();

		List<NameAndValueInfo> navis = new ArrayList<>();
		List<Object> datas = session.createSQLQuery(sql).addScalar("F").addScalar("R").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			if (StringUtil.isNotBlank((String)os[0])) {
				NameAndValueInfo navi = new NameAndValueInfo();
				navi.setName((String)os[0]);
				navi.setValue((BigDecimal)os[1]);
				navis.add(navi);
			}
		}
		SessionRepository.closeCurrentSession();
		return navis;
	}

	@Override
	public ProductRankingDetail inquireYestodayEarnings() {
		Session session = SessionRepository.getCurrentSession();
		String sql = "SELECT SUM(c.RECEIPT_AMOUNT) AS r, b.PRODUCT_TYPE AS p FROM fin_finance_application b, fin_finance_money_statement c WHERE b.FINANCE_ID = c.FINANCE_ID AND (c.STATEMENT_SOURCE = 'QUARTER_SETTLEMENT_INTEREST' OR c.STATEMENT_SOURCE = 'QUARTER_SETTLEMENT') AND c.STATEMENT_D8 = " + DateUtils.parseDateInt(DateUtils.addDateField(DateUtils.now(), Calendar.DATE, -1)) + " GROUP BY b.PRODUCT_TYPE;";
		Map<String, List<BigDecimal>> map = new HashMap<>();

		ProductRankingDetail prd = new ProductRankingDetail();
		List<Object> datas = session.createSQLQuery(sql).addScalar("r").addScalar("p").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			switch (((Byte)os[1]).intValue()) {
			case 1:
				prd.setValue1((BigDecimal)os[0]);
				break;
			case 2:
				prd.setValue2((BigDecimal)os[0]);
				break;
			case 3:
				prd.setValue3((BigDecimal)os[0]);
				break;
			case 4:
				prd.setValue4((BigDecimal)os[0]);
				break;
			case 5:
				prd.setValue5((BigDecimal)os[0]);
				break;
			case 6:
				prd.setValue6((BigDecimal)os[0]);
				break;
			default:
				break;
			}
		}
		SessionRepository.closeCurrentSession();
		return prd;
	}
	
	@Override
	public ProductRankingDetail inquireSevenDayEarnings() {
		Session session = SessionRepository.getCurrentSession();
		String sql = "SELECT SUM(c.RECEIPT_AMOUNT) AS r, SUM(c.DISBURSEMENT_AMOUNT) AS d, c.STATEMENT_SOURCE AS s FROM fin_finance_money_statement c WHERE (c.STATEMENT_SOURCE = 'LOAN_NOTICE' OR c.STATEMENT_SOURCE = 'RECEIVABLES_WAREHOUSE' OR c.STATEMENT_SOURCE = 'RECEIVABLES') AND c.STATEMENT_D8 >= " + DateUtils.parseDateInt(DateUtils.addDateField(DateUtils.now(), Calendar.DATE, -7)) + " GROUP BY c.STATEMENT_SOURCE;";
		Map<String, List<BigDecimal>> map = new HashMap<>();

		ProductRankingDetail prd = new ProductRankingDetail();
		BigDecimal r = BigDecimal.ZERO;
		BigDecimal d = BigDecimal.ZERO;
		List<Object> datas = session.createSQLQuery(sql).addScalar("r").addScalar("d").addScalar("s").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			r = r.add((BigDecimal)os[0]);
			d = d.add((BigDecimal)os[1]);
		}
		prd.setValue1(r);
		prd.setValue2(d);
		SessionRepository.closeCurrentSession();
		return prd;
	}

	@Override
	public EnterpriseSummaryDetail inquireEnterpriseSummaryDetail(Long enterpriseId) {
		EnterpriseSummaryDetail detail = new EnterpriseSummaryDetail(); 
		detail.setEnterpriseId(enterpriseId);
		Session session = SessionRepository.getCurrentSession();
		String sql = "select * from view_fin_enterprise_creditline v where v.enterpriseId = " + enterpriseId;
		List<Object> datas = session.createSQLQuery(sql).addScalar("creditLine").addScalar("creditAlready").addScalar("creditLineRemain").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			detail.setCreditLine((BigDecimal)os[0]);
			detail.setCreditLineAlready((BigDecimal)os[1]);
			detail.setCreditLineRemain((BigDecimal)os[2]);
		}

		sql = "select * from view_fin_finance_amount v where v.enterpriseId = " + enterpriseId;
		datas = session.createSQLQuery(sql).addScalar("enterpriseId").addScalar("financeAmount").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			detail.setFinanceAmount((BigDecimal)os[1]);
		}

		sql = "select * from view_fin_amount_by_product_type v where v.enterpriseId = " + enterpriseId;
		datas = session.createSQLQuery(sql).addScalar("productType").addScalar("amount").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			switch (((Byte)os[0]).intValue()) {
			case 1:
				detail.setFinanceAmount1((BigDecimal)os[1]);
				break;
			case 2:
				detail.setFinanceAmount2((BigDecimal)os[1]);
				break;
			case 3:
				detail.setFinanceAmount3((BigDecimal)os[1]);
				break;
			case 4:
				detail.setFinanceAmount4((BigDecimal)os[1]);
				break;
			case 5:
				detail.setFinanceAmount5((BigDecimal)os[1]);
				break;
			case 6:
				detail.setFinanceAmount6((BigDecimal)os[1]);
				break;
			default:
				break;
			}
		}

		sql = "select * from view_fin_loan_receipt_amout v where v.enterpriseId = " + enterpriseId;
		datas = session.createSQLQuery(sql).addScalar("receiptAmount").addScalar("disbursementAmount").addScalar("waitReceipt").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			detail.setReceiptAmount((BigDecimal)os[0]);
			detail.setDisbursementAmount((BigDecimal)os[1]);
			detail.setWaitReceipt((BigDecimal)os[2]);
		}

		sql = "select * from view_fin_number_by_product_type v where v.enterpriseId = " + enterpriseId;
		datas = session.createSQLQuery(sql).addScalar("productType").addScalar("c").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			switch (((Byte)os[0]).intValue()) {
			case 1:
				detail.setFinanceNumber1(((BigInteger)os[1]).intValue());
				break;
			case 2:
				detail.setFinanceNumber2(((BigInteger)os[1]).intValue());
				break;
			case 3:
				detail.setFinanceNumber3(((BigInteger)os[1]).intValue());
				break;
			case 4:
				detail.setFinanceNumber4(((BigInteger)os[1]).intValue());
				break;
			case 5:
				detail.setFinanceNumber5(((BigInteger)os[1]).intValue());
				break;
			case 6:
				detail.setFinanceNumber6(((BigInteger)os[1]).intValue());
				break;
			default:
				break;
			}
		}

		sql = "select * from view_fin_incustody_amount1 v where v.enterpriseId = " + enterpriseId;
		datas = session.createSQLQuery(sql).addScalar("enterpriseId").addScalar("amount").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			detail.setInCustodyBulkstockAmount((BigDecimal)os[1]);
		}

		sql = "select * from view_fin_incustody_amount2 v where v.enterpriseId = " + enterpriseId;
		datas = session.createSQLQuery(sql).addScalar("enterpriseId").addScalar("amount").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			detail.setInCustodyBulkstockAmount(detail.getInCustodyBulkstockAmount().add((BigDecimal)os[1]));
		}

		sql = "select * from view_fin_bulkstock_amount v where v.enterpriseId = " + enterpriseId;
		datas = session.createSQLQuery(sql).addScalar("enterpriseId").addScalar("amount").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			detail.setBulkstockAmount((BigDecimal)os[1]);
		}

		SessionRepository.closeCurrentSession();
		return detail;
	}

	@Override
	public PageList<ProcessFinanceApplication> inquireProcessFinanceApplicationPageList(Long enterpriseId, Integer productType, PageCriteria page) {
		PageList<ProcessFinanceApplication> pages = null;
		Session session = SessionRepository.getCurrentSession();
		String sql = "select count(*) c from view_fin_process_finance_application v where v.enterpriseId = " + enterpriseId;
		if (productType != null) {
			sql = sql + " and v.productType = " + productType;
		}
		List<Object> datas = session.createSQLQuery(sql).addScalar("c").list();
		for (Object l : datas) {
			Object os = (Object)l;
			pages = new PageList<>(page, ((BigInteger)os).intValue());
		}
		sql = "select * from view_fin_process_finance_application v where v.enterpriseId = " + enterpriseId;
		if (productType != null) {
			sql = sql + " and v.productType = " + productType;
		}
		sql = sql + " ORDER BY v.financeId DESC " + " LIMIT " + (page.getPageIndex() - 1)*page.getPageSize() + ", " + page.getPageSize();
		datas = session.createSQLQuery(sql).addScalar("financeId").addScalar("financeNumber").addScalar("productType").addScalar("financeAmount").addScalar("financeState").addScalar("bailRate").addScalar("loanAmount").addScalar("receiptAmount").addScalar("waitReceipt").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			ProcessFinanceApplication pfa = new ProcessFinanceApplication();
			pfa.setFinanceId(((BigInteger)os[0]).longValue());
			pfa.setFinanceNumber((String)os[1]);
			pfa.setProductType(((Byte)os[2]).intValue());
			pfa.setFinanceAmount((BigDecimal)os[3]);
			pfa.setFinanceState(((Byte)os[4]).intValue());
			pfa.setLoanAmount((BigDecimal)os[6]);
			pfa.setReceiptAmount((BigDecimal)os[7]);
			pfa.setWaitReceipt((BigDecimal)os[8]);
			List<FinanceBulkstockEntity> fbes = financeBulkstockService.inquireFinanceBulkstockList(pfa.getFinanceId());
			if (fbes != null && fbes.size() > 0) {
				for (FinanceBulkstockEntity fbe : fbes) {
					pfa.setBulkstockName(fbe.getBulkstockName());
					pfa.setSpecification(fbe.getSpecification());
					pfa.setEvaluatePrice(fbe.getEvaluatePrice());
					pfa.setEvaluation(pfa.getEvaluation().add(fbe.getEvaluatePrice().multiply(fbe.getValuationQuantity())));
					pfa.setFinancePrice(fbes.get(0).getFinancePrice());
					pfa.setInCustody(inquireInCustody(pfa.getFinanceId()));
					pfa.setFinalPrice(fbe.getFinalPrice());
					pfa.setWeight(fbe.getWeight().add(pfa.getWeight()));
				}
			}

			FinanceProductInnerEntity innerEntity = financeProductInnerService.inquireFinanceProductInner(pfa.getFinanceId(), null);
			if (innerEntity != null) {
				pfa.setInterestRate(innerEntity.getInterest());
				pfa.setFinanceDays(innerEntity.getFinanceDays());

				pfa.setBailRate(innerEntity.getBailRate());

				if (pfa.getFinalPrice() != null && pfa.getEvaluation() != null && pfa.getEvaluation().compareTo(BigDecimal.ZERO) != 0) {
					pfa.setPledgeRate((pfa.getFinalPrice().divide(pfa.getEvaluation(), BigDecimal.ROUND_HALF_UP)).toString());
				} else {
					pfa.setPledgeRate((pfa.getFinanceAmount().divide(pfa.getEvaluation(), 2, BigDecimal.ROUND_HALF_UP)).toString());
				}
			}

			pages.add(pfa);
		}

		SessionRepository.closeCurrentSession();
		return pages;
	}

	private BigDecimal inquireInCustody(Long financeId) {
		Session session = SessionRepository.getCurrentSession();

		BigDecimal inCustody = BigDecimal.ZERO;
		String sql = "select * from view_fin_incustody_quantity1 v where v.financeId = " + financeId;
		List<Object> datas = session.createSQLQuery(sql).addScalar("quantity").addScalar("financeId").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			inCustody = inCustody.add((BigDecimal)os[0]);
		}

		sql = "select * from view_fin_incustody_quantity2 v where v.financeId = " + financeId;
		datas = session.createSQLQuery(sql).addScalar("WEIGHT").addScalar("financeId").list();
		for (Object l : datas) {
			Object[] os = (Object[])l;
			inCustody = inCustody.add((BigDecimal)os[0]);
		}

		SessionRepository.closeCurrentSession();
		return inCustody;
	}
}
